package cn.ftx.mapper;

import cn.ftx.entity.Fangtianxia;
import cn.ftx.model.*;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;


/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author AUTHER
 * @since 2021-04-29
 */
public interface FangtianxiaMapper extends BaseMapper<Fangtianxia> {

    List<HouseTypeDTO> queryHouseTypeRatio();

//    @Select("        select rank ,\n" +
//            "        SUM(IF(`price` <= 100,1,0)) AS oneNumber,\n" +
//            "        SUM(IF(`price` > 100 AND `price` <= 200,1,0)) AS twoNumber,\n" +
//            "        SUM(IF(`price` > 200 AND `price` <= 300,1,0)) AS threeNumber,\n" +
//            "        SUM(IF(`price` > 300,1,0)) AS fourNumber,\n" +
//            "        COUNT(id) as totalNumber\n" +
//            "        from fangtianxia GROUP BY rank ORDER BY rank;")
    List<HouseRankDTO> queryHouseRankRatio();

    @Select(" SELECT ROUND(area) FROM fangtianxia GROUP BY(ROUND(area)) ORDER BY area;")
    List<Double> queryHouseAreaList();

    @Select("select build_time from fangtianxia group by build_time order by build_time;")
    List<String> queryHouseYearList();

    @Select("SELECT build_time,AVG(price) AS avgPrice FROM fangtianxia GROUP BY build_time ORDER BY build_time;")
    List<HouseYearPriceVO> queryYearPriceList();

    @Select("SELECT ROUND(area) as area,AVG(price) AS avgPrice FROM fangtianxia GROUP BY ROUND(area) ORDER BY area;")
    List<HouseAreaPriceVO> queryHouseAreaPriceList();

    @Select("SELECT build_time,type,COUNT(id) as number FROM fangtianxia GROUP BY build_time,type ORDER BY build_time;")
    List<HouseYearTypeDTO> queryYearTypeList();

    List<HouseTypePriceDTO> queryTypePriceList();
}
